package com.ideabove.heart.db;

import java.util.ArrayList;
import java.util.Hashtable;

import com.ideabove.heart.manager.ScheduleManager;
import com.ideabove.heart.schedule.ScheduleInfo;
import com.ideabove.heart.util.LogUtil;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper{
	private static final String DATABASE_NAME = "moealarm.db";
    private static final int DATABASE_VERSION = 1;
    public static SQLiteDatabase mDB;
    private DatabaseHelper mDBHelper;
    private Context mCtx;
 
    private class DatabaseHelper extends SQLiteOpenHelper{
 
        public DatabaseHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }
 
        @Override
        public void onCreate(SQLiteDatabase db) {
        	db.execSQL(Database.SeceduleTable._CREATE);
        	db.execSQL(Database.AlarmSetTable._CREATE);
        }
 
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//            db.execSQL("DROP TABLE IF EXISTS "+Database.AlarmSetTable._TABLENAME);
//            db.execSQL("DROP TABLE IF EXISTS "+Database.SeceduleTable._TABLENAME);
//            onCreate(db);
        }
    }
 
    public DBHelper(Context context){
        this.mCtx = context;
    }    
 
    public DBHelper open() throws SQLException{
    	if(mDB ==null || !mDB.isOpen()){
	        mDBHelper = new DatabaseHelper(mCtx, DATABASE_NAME, null, DATABASE_VERSION);
	        mDB = mDBHelper.getWritableDatabase();
    	}
        return this;
    }
    
    public ArrayList<ScheduleInfo> getScheduleInfoList(){
    	open();
    	
    	ArrayList<ScheduleInfo> infoList = new ArrayList<ScheduleInfo>();
    	
    	StringBuffer sb = new StringBuffer("SELECT ");
    	sb.append(Database.SeceduleTable._ID);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.STORY_STEP);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.SUCESS_TIME);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.EP);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.NEXT_EP);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.RP_IMG);    	
    	sb.append(" FROM ");
    	sb.append(Database.SeceduleTable._TABLENAME);
    	sb.append(" WHERE ");
    	sb.append(Database.SeceduleTable._ID);
    	sb.append(" < ");
    	sb.append(" 31 ");
    
    	Cursor cursor = mDB.rawQuery(sb.toString(),null);
    	    	
    	
    	while(cursor.moveToNext()){
    		ScheduleInfo sc = new ScheduleInfo();    		    	
    		sc.setId(cursor.getInt(0));
    		sc.setStep(cursor.getInt(1));
    		sc.setSucessTime(cursor.getString(2)); 
    		sc.setEp(cursor.getString(3));
    		sc.setNextEp(cursor.getString(4));
    		sc.setRpImg(cursor.getString(5));
    		
    		LogUtil.e(cursor.getInt(0) + "RP:" + cursor.getString(5));
    		
    		infoList.add(sc);        	
    	}
    	
    	close();
    	
    	return infoList;
    }
    
    public ScheduleInfo getScheduleInfo(){
    	open();
    	StringBuffer sb = new StringBuffer("SELECT COUNT(");
    	sb.append(Database.SeceduleTable.NEXT_EP);    	
    	sb.append(") FROM ");
    	sb.append(Database.SeceduleTable._TABLENAME);
    	
    	
    	Cursor c = mDB.rawQuery(sb.toString(), null);
    	
    	ScheduleInfo sInfo = new ScheduleInfo();
    	
    	LogUtil.e(""+c);
    	if(c.moveToLast()){
    		int cnt = c.getInt(0);
    		if(cnt > 0){
    			// 스케쥴이 있다면.
    			sInfo = getLastSchedule();
    		}else{
    			// 처음 유저라면. 없다면..
	        	addFirstSchedule(0,0,"ep0","ep1","rp_cute.png");	        	 // 처음 스케줄 등록.
    			sInfo.setId(-1);
    		}
    	}
    	close();
    	
    	return sInfo;
    }
    
    public ScheduleInfo getLastSchedule(){
    	
    	open();
    	ScheduleInfo sc = new ScheduleInfo();
    	
    	StringBuffer sb = new StringBuffer("SELECT ");
    	sb.append(Database.SeceduleTable._ID);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.STORY_STEP);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.SUCESS_TIME);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.EP);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.NEXT_EP);
    	sb.append(",");
    	sb.append(Database.SeceduleTable.RP_IMG);    	
    	sb.append(" FROM ");
    	sb.append(Database.SeceduleTable._TABLENAME);
    
    	Cursor cursor = mDB.rawQuery(sb.toString(),null);
    	    	
    	
    	if(cursor.moveToLast()){    		
    		sc.setId(cursor.getInt(0));
    		sc.setStep(cursor.getInt(1));
    		sc.setSucessTime(cursor.getString(2)); 
    		sc.setEp(cursor.getString(3));
    		
    		LogUtil.e("#PutNextEp###" + cursor.getString(4));
    		
    		sc.setNextEp(cursor.getString(4));
    		sc.setRpImg(cursor.getString(5));
    	}
    	close();
    	
    	return sc;
    }

    // 처음 스케줄을 넣는다.
    public void addFirstSchedule(long time, int step,String ep,String epName, String rpImg){
    	open();
    	StringBuffer sb = new StringBuffer("INSERT INTO "+ Database.SeceduleTable._TABLENAME+" ( ");
    	sb.append(Database.SeceduleTable.SUCESS_TIME);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.STORY_STEP);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.EP);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.NEXT_EP);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.RP_IMG);    	
    	sb.append(") VALUES (");
    	
    	sb.append(String.valueOf(time));
    	sb.append(",");
    	
    	sb.append(String.valueOf(step));
    	sb.append(",");
    	
    	sb.append("'" +ep + "'");
    	sb.append(",");
    	
    	sb.append("'" +epName + "'");
    	sb.append(",");
    	
    	sb.append("'" +rpImg + "'");    	    
    	sb.append(")");
    	

    	LogUtil.e(sb.toString());
    	mDB.execSQL(sb.toString());
    	close();
    }
    
 // 다음 스케줄을 넣는다.
    public void insertNextSchedule(long time, int step,String ep, String epName,String rpImg){
    	open();
    	StringBuffer sb = new StringBuffer("INSERT INTO "+ Database.SeceduleTable._TABLENAME+" ( ");
    	sb.append(Database.SeceduleTable.SUCESS_TIME);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.STORY_STEP);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.EP);
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.NEXT_EP);    	
    	sb.append(",");
    	
    	sb.append(Database.SeceduleTable.RP_IMG);    	
    	sb.append(") VALUES (");
    	
    	sb.append(String.valueOf(time));
    	sb.append(",");
    	
    	sb.append(String.valueOf(step));
    	sb.append(",");
    	
    	sb.append("'" +ep + "'");
    	sb.append(",");
    	
    	sb.append("'" +epName + "'");
    	sb.append(",");
    	
    	sb.append("'" +rpImg + "'");
    	sb.append(")");
    	

    	LogUtil.e(sb.toString());
    	mDB.execSQL(sb.toString());
    	close();
    }
    
    
    // 스케쥴을 업데이트 한다
    public void updateSchedule(int id, long time, int step){
    	open();
    	String query = "UPDATE " +Database.SeceduleTable._TABLENAME+" SET "
    			 +	Database.SeceduleTable.SUCESS_TIME + "=" + time + ","
    			 +	Database.SeceduleTable.STORY_STEP + "=" + step
    		     + " WHERE "+Database.SeceduleTable._ID+" = " + id;

    	LogUtil.e(query);
    	mDB.execSQL(query);
    	close();
    }
    
    // 스케줄 삭제
    public void deleteSchedule(){
    	open();    	
    	
    	String query = "DELETE FROM " +Database.SeceduleTable._TABLENAME;
    	String query2 = "DELETE FROM sqlite_sequence WHERE name='"+ Database.SeceduleTable._TABLENAME +"'";
    	
    	mDB.execSQL(query);
    	mDB.execSQL(query2);
    	
    	LogUtil.e(query2);
    	close();
    }
    
 // 스케줄 초기화
    public void initSchedule(){
    	
    	ScheduleInfo info = getLastSchedule();
    	int id = info.getId();
    	int step = info.getStep();
    	
    	open();
    	
    	String query = "UPDATE " +Database.SeceduleTable._TABLENAME+" SET "
    			 +	Database.SeceduleTable.NEXT_EP + "= 'ep0' ," 
    			 +	Database.SeceduleTable.STORY_STEP + "=" + step 
    		     + " WHERE "+Database.SeceduleTable._ID+" = " + id;

    	LogUtil.e(query + mDB.isOpen());
    	mDB.execSQL(query);    	
    	close();    	
    }
    

    public void episodeDone(){
    	
    	ScheduleInfo info = getLastSchedule();
    	updateSchedule(info.getId(), info.getAlarmTime(), ScheduleManager.STORY_DONE);
    }
    

    //  업데이트 알람 실패
    public void updateAlarmFail(int id, int failCnt){
    	open();
    	String query = "UPDATE " +Database.AlarmSetTable._TABLENAME+" SET "
    			 +	Database.AlarmSetTable.FAILCNT + "=" + failCnt
    		     + " WHERE "+Database.AlarmSetTable._ID+" = " + id;

    	LogUtil.e(query);
    	mDB.execSQL(query);
    	close();
    }
    
    //  업데이트 알람 성공
    public void updateAlarmSucess(int id, long sucessTime){
    	open();
    	String query = "UPDATE " +Database.SeceduleTable._TABLENAME+" SET "
    			 +	Database.SeceduleTable.SUCESS_TIME + "=" + sucessTime
    		     + " WHERE "+Database.AlarmSetTable._ID+" = " + id;

    	LogUtil.e(query);
    	mDB.execSQL(query);
    	close();
    }
    
    //  알람 받는거 실패 초기화
    public void updateFailCnt(int id, int failCnt){
    	open();
    	String query = "UPDATE " +Database.AlarmSetTable._TABLENAME+" SET "
    			 +	Database.AlarmSetTable.FAILCNT + "=" + failCnt
    		     + " WHERE "+Database.AlarmSetTable._ID+" = " + id;

    	LogUtil.e(query);
    	mDB.execSQL(query);
    	close();
    }
    
    
    // 알람을 다시 세팅한다.
    public void resetAlarm(int day, int hour, int min, long time, int isuse, int interval, int repeatCnt){
    	
    	mDB.execSQL("DELETE FROM " +  Database.AlarmSetTable._TABLENAME);
    	String query2 = "DELETE FROM sqlite_sequence WHERE name='"+ Database.AlarmSetTable._TABLENAME +"'";
    	mDB.execSQL(query2);
    	
    	StringBuffer sb = new StringBuffer("INSERT INTO "+ Database.AlarmSetTable._TABLENAME+" ( ");
    	
    	sb.append(Database.AlarmSetTable._ID);    	
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.DAY);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.HOUR);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.MIN);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.SETTIME);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.ISUSE);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.FAILCNT);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.INTERVAL);
    	sb.append(",");
    	
    	sb.append(Database.AlarmSetTable.REPEATE);
    	sb.append(") VALUES (");
    	
    	
    	sb.append(String.valueOf(1));
    	sb.append(",");
    	sb.append(String.valueOf(day));
    	sb.append(",");
    	sb.append(String.valueOf(hour));
    	sb.append(",");
    	sb.append(String.valueOf(min));
    	sb.append(",");
    	sb.append(String.valueOf(time));
    	sb.append(",");
    	sb.append(String.valueOf(isuse));
    	sb.append(",");
    	sb.append("0");
    	sb.append(",");
    	sb.append(String.valueOf(interval));
    	sb.append(",");
    	sb.append(String.valueOf(repeatCnt));    	
    	sb.append(")");
    	
    	LogUtil.e(sb.toString());
    	mDB.execSQL(sb.toString());
    	
    }
    
    // 알람 정보 가져오기
    public Hashtable<String, String> getAlarmInfo(){
    	open();
    	
    	Hashtable<String, String> data = new Hashtable<String, String>();    	
    	StringBuffer sb = new StringBuffer("SELECT ");
    	sb.append(Database.AlarmSetTable._ID);    	
    	sb.append(",");
    	sb.append(Database.AlarmSetTable.DAY);    	
    	sb.append(",");    	
    	sb.append(Database.AlarmSetTable.HOUR);
    	sb.append(",");    	
    	sb.append(Database.AlarmSetTable.MIN);
    	sb.append(",");    	
    	sb.append(Database.AlarmSetTable.SETTIME);
    	sb.append(",");    	
    	sb.append(Database.AlarmSetTable.ISUSE);
    	sb.append(",");   
    	sb.append(Database.AlarmSetTable.FAILCNT);
    	sb.append(",");
    	sb.append(Database.AlarmSetTable.INTERVAL);
    	sb.append(",");    
    	sb.append(Database.AlarmSetTable.REPEATE);    	
    	sb.append(" FROM ");
    	sb.append(Database.AlarmSetTable._TABLENAME);    	
    	
//    	LogUtil.e(sb.toString());
    	
    	Cursor c = mDB.rawQuery(sb.toString(), null);    	
    	
    	if(c.moveToLast()){
    		data.put(Database.AlarmSetTable._ID, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable._ID) ));
    		data.put(Database.AlarmSetTable.DAY, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.DAY) ));
    		data.put(Database.AlarmSetTable.HOUR, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.HOUR) ));
    		data.put(Database.AlarmSetTable.MIN, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.MIN) ));
    		data.put(Database.AlarmSetTable.SETTIME, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.SETTIME) ));    		
    		data.put(Database.AlarmSetTable.ISUSE, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.ISUSE) ));
    		data.put(Database.AlarmSetTable.FAILCNT, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.FAILCNT) ));   
    		data.put(Database.AlarmSetTable.INTERVAL, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.INTERVAL) ));
    		data.put(Database.AlarmSetTable.REPEATE, 
    				c.getString( c.getColumnIndex(Database.AlarmSetTable.REPEATE) ));
    	}
    	close();
    	return data;
    }
    
 
    public void close(){
        mDB.close();
    }
 
}
